/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import model.entities.Shift;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.ShiftDAOService;

/**
 *
 * @author Luffy
 */
public class ShiftDAO implements ShiftDAOService {
    private static ShiftDAO shiftDAO;
    public static ShiftDAO getInstance(){
        if(shiftDAO==null){
            shiftDAO= new ShiftDAO();
        }
        return shiftDAO;
    }
    
    @Override
    public List<Shift> getShiftsList() {
     ArrayList<Shift> listShift = new ArrayList<Shift>();
     Connection conn = ConnectionFactory.getConnection();
     String sql = "select * from tbllShift where isActived = 'true'";
     PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
             Shift sh= new Shift();
             sh.setShiftID(rs.getInt("shiftID"));
             sh.setName(rs.getString("name"));
             sh.setStartHours(rs.getInt("startHours"));
             sh.setFinishHours(rs.getInt("finishHours"));
             sh.setStartMins(rs.getInt("startMins"));
             sh.setFinishMins(rs.getInt("finishMins"));
             listShift.add(sh);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
        }    
    return listShift;
    }
    
    @Override
    public Shift getShiftByID(int shiftID){
        Shift sh = new Shift();
        Connection conn = ConnectionFactory.getConnection();
        String sql = "SELECT * FROM tblShift WHERE ShiftID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shiftID);
            ResultSet rs =pstmt.executeQuery();
            sh.setShiftID(rs.getInt("ShiftID"));
            sh.setName(rs.getString("Name"));
            sh.setStartHours(rs.getInt("StartHours"));
            sh.setFinishHours(rs.getInt("FinishHours"));
            sh.setStartMins(rs.getInt("StartMins"));
            sh.setFinishMins(rs.getInt("FinishMins"));
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sh;
    }

    @Override
    public boolean insertShift(Shift Shift) {
        Connection conn = ConnectionFactory.getConnection();
        String query = "INSERT INTO tblShift VALUES(?,?,?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, Shift.getName());
            pstmt.setInt(2,Shift.getStartHours());
            pstmt.setInt(3,Shift.getStartMins());
            pstmt.setInt(4,Shift.getFinishHours());
            pstmt.setInt(5,Shift.getFinishMins());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean updateShift(Shift Shift) {
        Connection conn = ConnectionFactory.getConnection();
        String query = "UPDATE tblShift SET Name=?,StartHours=?,StartMins=?,FinishHours=?,FinishMins=?" +
                        "WHERE ShiftID=?";
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setString(1, Shift.getName());
            pstmt.setInt(2,Shift.getStartHours());
            pstmt.setInt(3,Shift.getStartMins());
            pstmt.setInt(4,Shift.getFinishHours());
            pstmt.setInt(5,Shift.getFinishMins());
            pstmt.setInt(6, Shift.getShiftID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean deleteShift(int shiftId) {
        String query="DELETE tblShift WHERE ShiftID=?";
        Connection conn = ConnectionFactory.getConnection();
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareCall(query);
            pstmt.setInt(1, shiftId);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public List<Shift> findShiftByID(int shiftId) {
        List<Shift> lstShift = new ArrayList<Shift>();
        Connection conn = ConnectionFactory.getConnection();
        String sql = "SELECT * FROM tblShift WHERE ShiftID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, shiftId);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Shift sh = new Shift();
                sh.setShiftID(rs.getInt("ShiftID"));
                sh.setName(rs.getString("Name"));
                sh.setStartHours(rs.getInt("StartHours"));
                sh.setFinishHours(rs.getInt("FinishHours"));
                sh.setStartMins(rs.getInt("StartMins"));
                sh.setFinishMins(rs.getInt("FinishMins"));
                lstShift.add(sh);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstShift;
    }

    @Override
    public List<Shift> findShiftByName(String shiftName) {
        List<Shift> lstShift = new ArrayList<Shift>();
        Connection conn = ConnectionFactory.getConnection();
        String sql = "SELECT * FROM tblShift WHERE ShiftID=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, shiftName);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Shift sh = new Shift();
                sh.setShiftID(rs.getInt("ShiftID"));
                sh.setName(rs.getString("Name"));
                sh.setStartHours(rs.getInt("StartHours"));
                sh.setFinishHours(rs.getInt("FinishHours"));
                sh.setStartMins(rs.getInt("StartMins"));
                sh.setFinishMins(rs.getInt("FinishMins"));
                lstShift.add(sh);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ShiftDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstShift;
    }
}
